﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using System.IO;

namespace DatabaseSynchronizer {

    public class SQLDBService : DBServiceBase, IDBService {


        public SqlConnection _connection;
        public SqlCommand _command;


        public SQLDBService(string connectionString) {

            ConnectionString = connectionString;
        }


        private void CreateCommand(string commandText) {

            _connection = new SqlConnection(ConnectionString);
            _command = _connection.CreateCommand();
            _command.CommandText = commandText;
            _command.CommandType = System.Data.CommandType.Text;

        }


        private void DisposeResources() {

            if (_connection != null) {
                try {
                    if (_connection.State != ConnectionState.Closed) {
                        _connection.Close();
                    }
                }
                catch { }
            }

            if (_command != null) {
                try {
                    _command.Dispose();
                }
                catch { }
            }

            if (_connection != null) {
                try {
                    _command.Dispose();
                }
                catch { }
            }
        }


        public bool CreateSchemaChangesTable(out string errorMsg) {

            bool result = false;
            errorMsg = string.Empty;

            try {

                CreateCommand(Sql_CreateSchemaChangeTable);

                _connection.Open();
                _command.ExecuteNonQuery();
                result = true;

                DisposeResources();
            }
            catch (Exception ex) {

                errorMsg = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
            }

            return result;

        }


        public bool ExecuteScriptFile(FileInfo scriptFile, out string errorMessage) {

            bool result = false;
            errorMessage = string.Empty;

            using (_connection = new SqlConnection(ConnectionString)) {

                _command = _connection.CreateCommand();
                Server server = new Server(new ServerConnection(_connection));

                try {

                    //Execute the script
                    string script = scriptFile.OpenText().ReadToEnd();

                    server.ConnectionContext.BeginTransaction();
                    server.ConnectionContext.ExecuteNonQuery(script);

                    _command.CommandType = CommandType.Text;
                    _command.CommandText = Sql_Insert_SchemaChange;
                    _command.Parameters.Clear();
                    _command.Parameters.AddWithValue("@PachNum", scriptFile.PatchId());
                    _command.Parameters.AddWithValue("@ScriptFile", scriptFile.Name);
                    _command.Parameters.AddWithValue("@ExecutionDate", DateTime.Now);
                    _command.Parameters.AddWithValue("@ExecutedBy", Environment.UserName);
                    _command.Parameters.AddWithValue("@IsLocal", false);
                    _command.ExecuteNonQuery();
                    server.ConnectionContext.CommitTransaction();

                    result = true;
                }
                catch (Exception ex) {

                    errorMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;

                    try { server.ConnectionContext.RollBackTransaction(); }
                    catch { }
                }

                try { _connection.Close(); }
                catch { }
            }

            return result;
        }


        public List<int> GetExecutedPatches(out string errorMessage) {

            var patches = new List<int>();
            errorMessage = string.Empty;

            try {

                CreateCommand(Sql_Select_ExecutePatches);
                _connection.Open();
                var reader = _command.ExecuteReader(CommandBehavior.CloseConnection);

                while (reader.Read()) {

                    int patchNumber = 0;
                    int.TryParse(reader[0] == DBNull.Value ? string.Empty : reader[0].ToString(), out patchNumber);
                    patches.Add(patchNumber);
                }

                DisposeResources();
            }
            catch (Exception ex) {
                errorMessage = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
            }

            return patches;
        }


        public bool MarkLocalPatchAsExecuted(FileInfo scriptFile, out string errorMsg) {

            var result = false;
            errorMsg = string.Empty;

            try {
                CreateCommand(Sql_Insert_SchemaChange);

                _command.Parameters.Clear();
                _command.Parameters.AddWithValue("@PachNum", scriptFile.PatchId());
                _command.Parameters.AddWithValue("@ScriptFile", scriptFile.Name);
                _command.Parameters.AddWithValue("@ExecutionDate", DateTime.Now);
                _command.Parameters.AddWithValue("@ExecutedBy", Environment.UserName);
                _command.Parameters.AddWithValue("@IsLocal", false);
                _connection.Open();
                _command.ExecuteNonQuery();

                DisposeResources();
                result = true;
            }
            catch (Exception ex) {

                result = false;
                errorMsg = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
            }

            return result;
        }


        public bool IsValidConnectionString(out string errorMsg) {

            errorMsg = string.Empty;

            try {
                CreateCommand(ConnStringTestSQL);
                _connection.Open();
                _command.ExecuteNonQuery();
                return true;
            }
            catch (ArgumentException ex) {
                errorMsg = "Imvalid connection string.";
                return false;
            }
            catch (Exception ex) {
                errorMsg = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
                return false;
            }
            finally {
                DisposeResources();
            }
        }


        public bool IsSchemaChangesTableCreated(out string errorMsg) {

            errorMsg = string.Empty;
            object result = null;

            try {

                CreateCommand(Sql_SchemaChangeTableExist);
                _connection.Open();
                result = _command.ExecuteScalar();

                DisposeResources();
            }
            catch (Exception ex) {
                errorMsg = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
            }

            return result != null && !string.IsNullOrEmpty(result.ToString()) && result.ToString().Trim() == "1";
        }


        public bool IsPatchExecuted(int patchNumber, out string errorMsg) {

            int patchFound = 0;
            errorMsg = string.Empty;
            object result = null;

            try {

                CreateCommand(Sql_Select_IsPatchExecuted);

                _command.Parameters.AddWithValue("@PatchNumber", patchNumber);
                _connection.Open();

                result = _command.ExecuteScalar();

                DisposeResources();
            }
            catch (Exception ex) {
                errorMsg = ex.InnerException == null ? ex.Message : ex.InnerException.Message;
            }

            if (result != null && !string.IsNullOrEmpty(result.ToString())) {

                int.TryParse(result.ToString(), out patchFound);
            }

            return patchFound == 1;
        }


    }
}
